package models

import (
	//"encoding/json"
	"github.com/astaxie/beego/logs"
	//"github.com/goinggo/mapstructure"
	"log"

	//"LogServer/controllers"
	//"database/sql"
	"errors"
	"fmt"
	"reflect"
	"strconv"
	"strings"
	//"strconv"
	"github.com/astaxie/beego/orm"
	//_ "github.com/weimingjue/json"
	//"github.com/mitchellh/mapstructure"
)
var db MysqlDB

func init() {
	orm.RegisterModel(new(ClassLog))
	ok:=db.Connect()
	if ok!=true{
	log.Fatal("数据库连接失败")
	}
}


//向class_log_test表加入一条记录
func InsertIntoClassLogTest (classlog *ClassLog) error{
	//orm := orm.NewOrm()
	//strSQL = "insert into class_log_test(action,bookid,classid,note,roomid,stuid,teaid,traceid,ts,uid) values(222,?,?,?,?,?,?,?,?,?)";
	classlogtsint,_:=strconv.Atoi(classlog.Ts)
	//_,err:=MysqlConnector.Exec("insert into class_log_test(action,bookid,classid,note,roomid,stuid,teaid,traceid,ts,uid) values(?,?,?,?,?,?,?,?,?,?)",classlog.Action,classlog.Bookid,classlog.Classid,classlog.Note,classlog.Roomid,classlog.Stuid,classlog.Teaid,classlog.Traceid,classlogtsint,classlog.Uid)
	_,err:=db.Exec("insert into class_log_test(action,bookid,classid,note,roomid,stuid,teaid,traceid,ts,uid) values(?,?,?,?,?,?,?,?,?,?)",classlog.Action,classlog.Bookid,classlog.Classid,classlog.Note,classlog.Roomid,classlog.Stuid,classlog.Teaid,classlog.Traceid,classlogtsint,classlog.Uid)
	//p, err := orm.Raw("insert into class_log_test(action,bookid,classid,note,roomid,stuid,teaid,traceid,ts,uid) values(?,?,?,?,?,?,?,?,?,?)").Prepare()
	if err!=nil{
		logs.Error(err)
		return err
	}
	//_, err1 := p.Exec(classlog.Action,classlog.Bookid,classlog.Classid,classlog.Note,classlog.Roomid,classlog.Stuid,classlog.Teaid,classlog.Traceid,classlogtsint,classlog.Uid)
	return nil
}
//获得某位老师在某个班上点过赞的同学的ID以及点赞数
func GetPraiseOfTeacherAndClass(teaid string,classid string) ([]*Student,error){
	//o:=orm.NewOrm()
	//type praise PraiseResp
	//var praises []*praise
	//type student Student
	var students []*Student
	//p,err:=o.Raw("select stuid,sum(case when action='AddPraise' THEN 1 else 0 end)-sum(case when action='DelPraise' then 1 else 0 end)	as scorefrom class_log_test where teaid="+teaid+" and classid="+classid+" group by stuid")
	//_,err=p.Exec(teaid,classid)

	//rows, err:=Mysql.Query("select stuid,sum(case when action='AddPraise' THEN 1 else 0 end)-sum(case when action='DelPraise' then 1 else 0 end)	as score from class_log_test where teaid=? and classid=? group by stuid",teaid,classid)
	//sql:="select stuid,sum(case when action='AddPraise' THEN 1 else 0 end)-sum(case when action='DelPraise' then 1 else 0 end)	as score from class_log_test where teaid="+classid+" and classid="+teaid+" group by stuid";
	//log.info.Println(sql);
	//fmt.Println()

	//rows,err:=db.Query(sql, MysqlConnector);

	rows,err:=db.Query("select stuid,sum(case when action='AddPraise' THEN 1 else 0 end)-sum(case when action='DelPraise' then 1 else 0 end)	as score from class_log_test where teaid=? and classid=? group by stuid",teaid,classid)
		if err!=nil{
		logs.Error(err)
		return nil,err
	}
	i:=0
	for rows.Next(){

		rows.Columns()
		var student Student
		students=append(students,&student)
		err=rows.Scan(&students[i].Stuid,&students[i].Score)
		i++
		if err!=nil{
			logs.Error(err)
		}
	}





	//for i:=0;i<len(students);i++{
	//	fmt.Print(*students[i],"   ")
	//}
	return	students,err
}
//查询学校智慧课堂授课教师情况，avg_teachtime 单位秒
func GetTeachingInfo(schoolid string) (int,int,error){
	//o:=orm.NewOrm()
	//n是曾经上过课的教师总数
	n:=0
	//rows,err:=Mysql.Query("select COUNT(*) from (SELECT DISTINCT teaid from class_log_test where schoolid=? and action='StartClass' group by teaid)A",schoolid)
	sql:=fmt.Sprintf("select COUNT(*) from (SELECT DISTINCT teaid from class_log_test where schoolid=%s and action='StartClass' group by teaid)A",schoolid)
	rows,err:=db.Query(sql)
	if err!=nil{
		logs.Error(err)
	}
	for rows.Next() {
		rows.Columns()

		err := rows.Scan( &n)
		if err!=nil{
			logs.Error(err)
			return 0,0,err
		}
	}

	//t是所有曾经上过课的教师的上课时长总和（单位：秒）
	t:=0
	rows,err=db.Query("SELECT sum(usetime) from class_log_test where schoolid=? and action='StopClass'",schoolid)
	for rows.Next() {
		rows.Columns()
		err := rows.Scan( &t)
		if err!=nil{
			logs.Error(err)
			return 0,0,err
		}
	}
	avg_teachtime:=t/n
	return n,avg_teachtime,nil
}

//测试
func TestSql() interface{}{
	fmt.Println("Mysql的值为：",db.MysqlConnector)
	res,err:=db.Exec("INSERT into class_log_test(action,bookid,classid,note,roomid,stuid,teaid,traceid,ts,uid) values('actionTest1111111111111111111111',666,555,'testNote','roomTest',1111,2222,'testTraceid',20191207094526,7777)")
	if err!=nil{
		logs.Error(err)
		fmt.Println(err)
	}
	err=db.MysqlConnector.Close()
	if err!=nil{
		fmt.Println(err)
	}
	return res
}

func uint8ToInt(arr []uint8) int32{
	i := arr[0] << 24 + arr[1] << 16 + arr[2] << 8 + arr[3]
	fmt.Println(i)
	return int32(i)
}

//查询学校智慧课堂授课时长在0~1小时的教师
func GetTimeTeachers(schoolid,mintime,maxtime string) ([]*TeacherTime,error){
	sql:="SELECT * from(SELECT uname,sum(usetime) time from class_log_test where schoolid="+schoolid+" and action='StopClass'  group by teaid)A where  time>"+mintime+"  and time<"+maxtime
	fmt.Println(sql)
	list,err:=db.Query1(sql)
	if err!=nil{
		logs.Error(err)
		return nil,err
	}
	var teacherTimes []*TeacherTime
	fmt.Println("list的值为：",list)
	for i:=range list{
		var teacherTime TeacherTime
		fmt.Println("list[",i,"]:",list[i])
		map1:=list[i]
		teacherTime.Name=string(map1["uname"].([]uint8))
		teacherTime.TeacherTime, err=strconv.Atoi(string(map1["time"].([]uint8)))
		fmt.Println(teacherTime)
		teacherTimes=append(teacherTimes,&teacherTime)
	}

	return teacherTimes,nil
}
//查询学校智慧课堂教师的授课频率
func GetTeachingRate(schoolid string)([]*TeachingRate,error){
	sql:=fmt.Sprintf("select  uname,uid,count(*) teachcount from class_log_test where schoolid=%s and action='StopClass' GROUP BY uid",schoolid)
	list,err:=db.Query1(sql)
	if err!=nil{
		logs.Error(err)
		return nil,err
	}
	var teachingRates []*TeachingRate
	for i:=range list{
		var teachingRate	TeachingRate
		map1:=list[i]
		teachingRate.Uid,_=strconv.Atoi(string(map1["uid"].([]uint8)))
		teachingRate.Teachcount,_=strconv.Atoi(string(map1["teachcount"].([]uint8)))
		teachingRate.Uname=string(map1["uname"].([]uint8))
		teachingRates=append(teachingRates,&teachingRate)
		fmt.Println(teachingRate)
	}

	return teachingRates,err
}

// 填充列表接口
func GetTeacherData(schoolid string) ([]*TeacherData,error){
	sql:=fmt.Sprintf("select a.*, b.preclass_times,b.preclass_count from(select uname,uid,sum(usetime) class_times,count(*) class_count from class_log_test where schoolid=%s and action='StopClass' GROUP BY uid) a Left JOIN(select uname,uid,sum(usetime) preclass_times,count(*) preclass_count from class_log_test where schoolid=%s and action='StopPreClass' GROUP BY uid) b on a.uid=b.uid and a.uname=b.uname",schoolid,schoolid)
	list,err:=db.Query1(sql)
	if err!=nil{
		logs.Error(err)
		return nil,err
	}
	var teacherDatas []*TeacherData
	for i:=range list{
		var teacherData TeacherData
		map1:=list[i]
		teacherData.Uid,_=strconv.Atoi(string(map1["uid"].([]uint8)))
		teacherData.Uname=string(map1["uname"].([]uint8))
		teacherData.ClassCount,_=strconv.Atoi(string(map1["class_count"].([]uint8)))
		teacherData.ClassTimes,_=strconv.Atoi(string(map1["class_times"].([]uint8)))
		teacherData.PreClassTimes,_=strconv.Atoi(string(map1["preclass_times"].([]uint8)))
		teacherData.PreClassCount,_=strconv.Atoi(string(map1["preclass_count"].([]uint8)))
		teacherDatas=append(teacherDatas,&teacherData)
		fmt.Println(teacherData)
	}
	return teacherDatas,err
}



//func GetTimeTeachers(schoolid,mintime,maxtime string) ([]*TeacherTime,error){
//	sql:="SELECT * from(SELECT uname,sum(usetime) time from class_log_test where schoolid="+schoolid+" and action='StopClass'  group by teaid)A where  time>"+mintime+"  and time<"+maxtime
//	//rows,err:=db.Query("SELECT * from(SELECT uname,sum(usetime) time from class_log_test where schoolid=? and action='StopClass'  group by teaid)A where  time>?  and time<?",schoolid,mintime,maxtime)
//	rows,err:=db.Query(sql)
//	if err!=nil{
//		logs.Error(err)
//		return nil,err
//	}
//	var teacherTimes []*TeacherTime
//	i:=0
//	for rows.Next(){
//		var teachertime TeacherTime
//		data,_:=rows.Columns()
//		fmt.Println("data的值为：",data)
//		err=rows.Scan(&teachertime.Name,&teachertime.TeacherTime)
//		if err!=nil{
//			logs.Error(err)
//			return nil,err
//		}
//		teacherTimes=append(teacherTimes,&teachertime)
//		i++
//	}
//	fmt.Println(teacherTimes)
//	return teacherTimes,nil
//}


//---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

// AddClassLog insert a new ClassLog into database and returns
// last inserted Id on success.
func AddClassLog(m *ClassLog) (id int64, err error) {
	o := orm.NewOrm()
	id, err = o.Insert(m)
	return
}

// GetClassLogById retrieves ClassLog by Id. Returns error if
// Id doesn't exist
func GetClassLogById(id int64) (v *ClassLog, err error) {
	o := orm.NewOrm()
	v = &ClassLog{Id: id}
	if err = o.QueryTable(new(ClassLog)).Filter("Id", id).RelatedSel().One(v); err == nil {
		return v, nil
	}
	return nil, err
}

// GetAllClassLog retrieves all ClassLog matches certain condition. Returns empty list if
// no records exist
func GetAllClassLog(query map[string]string, fields []string, sortby []string, order []string,
	offset int64, limit int64) (ml []interface{}, err error) {
	o := orm.NewOrm()
	qs := o.QueryTable(new(ClassLog))
	// query k=v
	for k, v := range query {
		// rewrite dot-notation to Object__Attribute
		k = strings.Replace(k, ".", "__", -1)
		qs = qs.Filter(k, v)
	}
	// order by:
	var sortFields []string
	if len(sortby) != 0 {
		if len(sortby) == len(order) {
			// 1) for each sort field, there is an associated order
			for i, v := range sortby {
				orderby := ""
				if order[i] == "desc" {
					orderby = "-" + v
				} else if order[i] == "asc" {
					orderby = v
				} else {
					return nil, errors.New("Error: Invalid order. Must be either [asc|desc]")
				}
				sortFields = append(sortFields, orderby)
			}
			qs = qs.OrderBy(sortFields...)
		} else if len(sortby) != len(order) && len(order) == 1 {
			// 2) there is exactly one order, all the sorted fields will be sorted by this order
			for _, v := range sortby {
				orderby := ""
				if order[0] == "desc" {
					orderby = "-" + v
				} else if order[0] == "asc" {
					orderby = v
				} else {
					return nil, errors.New("Error: Invalid order. Must be either [asc|desc]")
				}
				sortFields = append(sortFields, orderby)
			}
		} else if len(sortby) != len(order) && len(order) != 1 {
			return nil, errors.New("Error: 'sortby', 'order' sizes mismatch or 'order' size is not 1")
		}
	} else {
		if len(order) != 0 {
			return nil, errors.New("Error: unused 'order' fields")
		}
	}

	var l []ClassLog
	qs = qs.OrderBy(sortFields...).RelatedSel()
	if _, err = qs.Limit(limit, offset).All(&l, fields...); err == nil {
		if len(fields) == 0 {
			for _, v := range l {
				ml = append(ml, v)
			}
		} else {
			// trim unused fields
			for _, v := range l {
				m := make(map[string]interface{})
				val := reflect.ValueOf(v)
				for _, fname := range fields {
					m[fname] = val.FieldByName(fname).Interface()
				}
				ml = append(ml, m)
			}
		}
		return ml, nil
	}
	return nil, err
}

// UpdateClassLog updates ClassLog by Id and returns error if
// the record to be updated doesn't exist
func UpdateClassLogById(m *ClassLog) (err error) {
	o := orm.NewOrm()
	v := ClassLog{Id: m.Id}
	// ascertain id exists in the database
	if err = o.Read(&v); err == nil {
		var num int64
		if num, err = o.Update(m); err == nil {
			fmt.Println("Number of records updated in database:", num)
		}
	}
	return
}

// DeleteClassLog deletes ClassLog by Id and returns error if
// the record to be deleted doesn't exist
func DeleteClassLog(id int64) (err error) {
	o := orm.NewOrm()
	v := ClassLog{Id: id}
	// ascertain id exists in the database
	if err = o.Read(&v); err == nil {
		var num int64
		if num, err = o.Delete(&ClassLog{Id: id}); err == nil {
			fmt.Println("Number of records deleted in database:", num)
		}
	}
	return
}
